package com.glsc.ngateway.common.base.repo.mysql.gateway.platform.it;


import com.glsc.ngateway.common.base.domain.mysql.gateway.platform.it.ItServerDevice;
import com.glsc.ngateway.common.base.domain.mysql.gateway.platform.it.ItVirtualDevice;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.*;


@Repository
public interface ItServerDeviceRepo extends JpaRepository<ItServerDevice, Integer> {


    ItServerDevice findByDevId(Integer devId);


    @Query(value = "select m from ItServerDevice m where 1=1 and (:userid is null OR m.userId = :userid)  order by m.insertTime desc")
    Page<ItServerDevice> getServerDeviceByUser(Pageable pageable, String userid);


    @Query(value = "select m from ItServerDevice m left join SystemMain n on m.businessName = n.systemClassNo where 1=1 " +
            "and (:businessName is null OR m.businessName = :businessName )  " +
            "and (:devId is null OR m.devId = :devId )  " +
            "and (:applicationDetail is null OR m.applicationDetail = :applicationDetail )  " +
            "and (:opStyle is null OR n.mainClass = :opStyle )  " +
            "and (:capitalStyle is null OR m.capitalStyle = :capitalStyle)  " +
            "and (:machineRoom is null OR m.machineRoom = :machineRoom )  " +
            "and (:machineStatus is null OR m.machineStatus = :machineStatus )  " +
            "and (:operationSys is null OR m.operationSys = :operationSys )  " +
            "and (:userId is null OR m.userId = :userId)  " +
            "and (:machineCab is null OR m.machineCab LIKE CONCAT('%',:machineCab,'%'))  " +
            "and (:capitalTyped is null OR m.capitalTyped LIKE CONCAT('%',:capitalTyped,'%'))  " +
            "and (:capitalType is null OR m.capitalType LIKE CONCAT('%',:capitalType,'%'))  " +
            "and (:ip1 is null OR m.ip1 LIKE CONCAT('%',:ip1,'%'))  " +
            "and (:ip2 is null OR m.ip2 LIKE CONCAT('%',:ip2,'%'))  " +
            "and (:sn is null OR m.sn LIKE CONCAT('%',:sn,'%')) " +
            "and (:searchMacParam is null OR m.mac1 LIKE CONCAT('%',:searchMacParam,'%') OR m.mac2 LIKE CONCAT('%',:searchMacParam,'%') OR m.mac3 LIKE CONCAT('%',:searchMacParam,'%') OR m.mac4 LIKE CONCAT('%',:searchMacParam,'%')) " +
            "and (:searchIpParam is null OR m.ip1 LIKE CONCAT('%',:searchIpParam,'%') OR m.ip2 LIKE CONCAT('%',:searchIpParam,'%') OR m.ilo LIKE CONCAT('%',:searchIpParam,'%') ) " +
            "order by m.insertTime desc ")
    Page<ItServerDevice> searchServerDevice(String businessName, String applicationDetail, String opStyle, String machineRoom,
                                            String machineCab, String capitalTyped, String capitalType, String capitalStyle, String ip1, String ip2,
                                            String sn, String operationSys, String userId, String machineStatus, String searchMacParam, String searchIpParam, Pageable pageable,Integer devId);

    @Query(value = "select a from ItServerDevice a where 1=1 "+
              "and ((:ssd is null or sn like CONCAT('%',:ssd,'%')) " +
            "or (:ssd is null or ip1 like  CONCAT('%',:ssd,'%')) " +
            "or (:ssd is null or ip2 like  CONCAT('%',:ssd,'%')) " +
            "or (:ssd is null or mac1 like CONCAT('%',:ssd,'%')) " +
            "or (:ssd is null or mac2 like CONCAT('%',:ssd,'%')) " +
            "or (:ssd is null or ilo like  CONCAT('%',:ssd,'%'))) ")
    List<ItServerDevice> searchDevice(String ssd);

    @Query(value = "select a from ItServerDevice a where 1=1 "+
            "and ((:ssd is null or sn = :ssd ) " +
            "or (:ssd is null or ip1  = :ssd ) " +
            "or (:ssd is null or ip2  = :ssd ) " +
            "or (:ssd is null or mac1 = :ssd ) " +
            "or (:ssd is null or mac2 = :ssd ) " +
            "or (:ssd is null or ilo  = :ssd )) ")
    List<ItServerDevice> searchDeviceNotLike(String ssd);

    @Query(value = "select m from ItServerDevice m where 1=1 " +
            "and (m.businessName = :systemClassNo " +
            "and m.userId = :systemUserId " +
            "and (m.machineStatus is null or m.machineStatus in ('15','18') )" +
            "and m.devId != :devId) ")
    List<ItServerDevice> queryBySystemClassNoAndUserId(String systemClassNo, String systemUserId, Integer devId);

    @Query(value = "select m from ItServerDevice m where 1=1 " +
            "and (m.businessName = :systemClassNo " +
            "and m.userId = :systemUserId " +
            "and (m.machineStatus is null or m.machineStatus in ('15','18')))")
    List<ItServerDevice> queryBySystemClassNoAndUserIdWithOutID(String systemClassNo, String systemUserId);



    @Query(value = " select u from  ItServerDevice u where 1= 1 " +
            "and u.devId in :ids ")
    List<ItServerDevice> queryByIds(Set<Integer> ids);
}
